package com.wuchaooooo.service.zjutsds.dao;

import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import com.wuchaooooo.service.zjutsds.pojo.vo.VScore;

/**
 * Created by arthur on 01/30/2020.
 */
@Repository
public class ScoreDAO {
    private static final Logger LOGGER = LoggerFactory.getLogger(ScoreDAO.class);

    @Resource(name = "primaryJdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    public List<VScore> getUniList(VScore vScore) {
    	StringBuilder sql = new StringBuilder();
    	sql.append("SELECT es.uni_id, es.name , u.majorname as major, u.eval, u.dfc, u.nation, es.score2017, es.score2018, es.score2019");
    	sql.append(" FROM " + getTable() + " es");
    	sql.append(" INNER JOIN university_info u ON u.uni_id = es.uni_id AND u.subject = es.subject");
    	Map<String, Object> args  = new HashMap<>();
    	if ("2017".equals(vScore.getYear())) {
        	sql.append(" WHERE  es.score2017 BETWEEN :score1 AND :score2");
    	} else if ("2018".equals(vScore.getYear())) {
        	sql.append(" WHERE  es.score2018 BETWEEN :score1 AND :score2");
    	}else if ("2019".equals(vScore.getYear())) {
        	sql.append(" WHERE  es.score2019 BETWEEN :score1 AND :score2");
    	}
    	sql.append(" AND es.subject = :subject");
    	sql.append(" AND es.province = :province");
    	if(!StringUtils.isEmpty(vScore.getLocation())) {
        	args.put("targetProvince", Arrays.asList(vScore.getLocation().split(",")));
        	sql.append(" AND u.province IN (:targetProvince)");
    	}
    	if(!StringUtils.isEmpty(vScore.getCategoryCode())) {
        	args.put("majorCategoryCode", Arrays.asList(vScore.getCategoryCode().split(",")));
        	sql.append(" AND u.majorCategoryCode IN (:majorCategoryCode)");
    	}
    	if(!StringUtils.isEmpty(vScore.getName())) {
        	args.put("name", vScore.getName());
        	sql.append(" AND u.NAME LIKE '%:name%'");
    	}
    	if ("2017".equals(vScore.getYear())) {
    		sql.append(" ORDER BY es.score2017 DESC,");
    	} else if ("2018".equals(vScore.getYear())) {
    		sql.append(" ORDER BY es.score2018 DESC, ");
    	}else if ("2019".equals(vScore.getYear())) {
    		sql.append(" ORDER BY es.score2019 DESC, ");
    	}
    	sql.append("es.name, case when instr(u.eval, '(A+)') > 0 then 1 when instr(u.eval, '(A)') > 0 then 2 when instr(u.eval, '(A-)') > 0 then 3 when instr(u.eval, '(B+)') > 0 then 4 when instr(u.eval, '(B)') > 0 then 5 when instr(u.eval, '(B-)') > 0 then 6 when instr(u.eval, '(C+)') > 0 then 7 when instr(u.eval, '(C)') > 0 then 8 when instr(u.eval, '(C-)') > 0 then 9 ELSE 10 end");

        try {
        	NamedParameterJdbcTemplate nameJdbc = new NamedParameterJdbcTemplate(jdbcTemplate);
            Integer score = Integer.valueOf(vScore.getScore());
        	args.put("score1", score - 10);
            args.put("score2", score + 5 );
            args.put("subject", vScore.getSubject());
            args.put("province", vScore.getProvince()); 
            LOGGER.info("SQL语句：" + sql.toString());
            LOGGER.info("参数为：" + args.toString());
            return nameJdbc.query(sql.toString(), args, new BeanPropertyRowMapper<VScore>(VScore.class));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    private String getTable() {
        return "score";
    }

}
